/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 *
 * @author issan
 */
public class Query_Advisor {
    
    private static HashMap advisors;
    
    public static Object query_dati_advisor(String nome,int id){
        
        
        if(advisors==null){
            advisors = new HashMap(150);  
        }
        if(advisors.containsKey(nome)){
            return advisors.get(nome);
        }
        
       Advisor x = new Advisor();
   
       String query = ("  Select * from "
+" (SELECT supervisor_ID ,first_name,last_name,email FROM supervisors where supervisor_ID = '"+id+"' and can_be_advisor=1) as adv "
+"  LEFT JOIN "
+" ( SELECT user_ID ,first_name,last_name,current_advisor FROM students ) as stud ON stud.current_advisor = adv.supervisor_ID; "
);

     
        
       List <Integer> studenti = null;
       try 
       {
        Connection con = DBconnection.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
       
        
       studenti = new ArrayList <Integer>();
        while ( rs.next()) {
            Integer studente = rs.getInt("stud.user_ID") ;
            studenti.add(studente);      
         }
       con.close();
         
       }
       catch(Exception ex){
           ex.printStackTrace();
       }

       
       x.setStudenti(Query_Studente.query_lista_studenti(studenti));
       advisors.put(nome, x);
     return x;
    }  
    
    
    public static boolean query_modifica_voto(String nome,int id_adv,int id_corso, String id_studente,String approvato ,String voto){
        
        Advisor advisor  = (Advisor) advisors.get(nome);
        if(advisor==null) return false;
        
        if(approvato.contentEquals("approvato")){
            Date now = new Date();
            SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            approvato = formato.format(now);
        }else{
            approvato = null;
        }
        
  
         
        
        String query = "UPDATE courses_enrollments SET qualification="+voto+" , is_finished=1, updated_at='"+approvato+"'  WHERE student="+id_studente+" AND course="+id_corso+";";
        try 
        {
            Connection con = DBconnection.getConnection();
            Statement stmt = con.createStatement();
            stmt.executeUpdate(query);
            con.close();
        }
        catch(Exception ex){
           
           ex.printStackTrace();
           return false;
        }
        
        
        
        advisors.remove(nome);
        query_dati_advisor(nome,id_adv);
        
        
        return true;
    }
    
       public static boolean query_approva_corso(String nome,int id_adv,int id_corso, String id_studente,String approvato){
        
        Advisor advisor  = (Advisor) advisors.get(nome);
        if(advisor==null) return false;
        
        if(approvato.contentEquals("approvato")){
            Date now = new Date();
            SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            approvato = formato.format(now);
        }else{
            approvato = null;
        }
        
        String query;
        if(approvato==null)
            query = "DELETE FROM courses_enrollments WHERE student="+id_studente+" AND course="+id_corso+";";
        else
            query = "UPDATE courses_enrollments SET approved_at='"+approvato+"'  WHERE student="+id_studente+" AND course="+id_corso+";";
        try 
        {
            Connection con = DBconnection.getConnection();
            Statement stmt = con.createStatement();
            stmt.executeUpdate(query);
            con.close();
        }
        catch(Exception ex){
           
           ex.printStackTrace();
           return false;
        }

        advisors.remove(nome);
        query_dati_advisor(nome,id_adv);
        
        
        return true;
    }
    
    
            public static void delete(String user)
                {
                    if(advisors != null)
                        if(advisors.containsKey(user))
                            advisors.remove(user);
                }

    
}
